The present invention generally relates to a method and system for importing database information into a querying system.
Numerous independently owned collections of data are being created and maintained all over the world. The number of active and legacy data sources almost guarantees that part or all of a query can be answered using one of these countless databases. However, there exist several intermediate steps between posing a query and receiving an answer that make the task of querying others"" databases almost impossible for the average user. First, the user must locate a relevant data source. Then he or she must gain access to the source, pose the query using table names and attribute names from that target database, and finally must decide which of the returned data, if any, is relevant to the query. User""s queries must be formatted correctly, either using structured query language (SQL) code, or using formatted blocks of code (i.e., code generated by a back-end process based on user-filled selection boxes and text fields).
While this list of steps is formidable, the process of querying is even more difficult if multiple databases must be consulted to obtain a complete answer. Not only must the above steps be executed, but the data from different sources must be joined; and, if there are discrepancies, the user must decide which source is more reliable. In integrating the data, users must first understand elements of each database""s schema so that corresponding fields between databases can be identified. Even once corresponding fields have been located, user must consider both the relative accuracy of the sources and the timeliness of the data contained within the sources. For example, data in a five (5) year old database would obviously be less relevant to data in a current database if a Department of Defense (DoD) member is querying about current troop movements.
There are even more basic problems standing between the user""s query and an answering data set. Databases are created with a particular task in mind. The database may be tailored for ease of asking particular types of queries, for ease of storing new data, or for storing groups of attributes as an object. Designing databases for specific purposes allows data to be stored and retrieved efficiently for that particular task and possibly a few related tasks. However, this makes it nearly impossible to retrieve information for other unrelated tasks. In looking at the task of querying from this perspective, it can be seen that the most fundamental querying problem is that groupings of objects that make sense in one database representation, make it difficult to regroup attributes to form objects meaningful to a query unrelated to the database""s specific purpose. For example, consider the database tables below which have been excerpted from a hypothetical company""s relational database:
Tables and attributes from a hypothetical company database. The xe2x80x9cEmployeexe2x80x9d table has key Employee_ID and attributes Social_Sec_#, Salary, and Title. The xe2x80x9cAcquisition Agentxe2x80x9d table has key Occupation Code and attributes Salary_Band_A, Salary_Band_B,and Band_A_Max_PO.
A division of this hypothetical company has a database that keeps track of its employees. The database has a table, xe2x80x9cEmployee, xe2x80x9d that contains basic information such as name, social security number, salary, and job title. The key in this table is Employee_ID. The database also has individual tables relating to each job title within the company. These tables note the occupation""s salary ranges (e.g., Salary_Band_A) and the specific duties at each salary level (e.g., Band_A_Max_PO). For example, for an xe2x80x9cAcquisition_Agent, xe2x80x9d the salary bands are A, B, etc., and the maximum amount that an individual in salary band A may purchase is Band_A_Max_PO. This table""s key is Occupation_Code. A reasonable query from another division of the company could be xe2x80x9cReturn the individuals who can purchase more than 5000 units of product X.xe2x80x9d Given the above two tables from the database, we can see that the query will be difficult to execute. First, the individual asking the query would have to know that Acquisition_Agent and Buyer were synonymous. Next, a join on salary would need to be executed, but there is no common key. Finally, math would have to be performed to translate between the maximum purchase order allowed (Band_A_Max_PO) and the number of units of X a specific buyer could purchase. This seemingly simple query requires a great deal of database-specific knowledge.
From the above discussion it is clear that there can be a number of issues encountered in trying to retrieve data from an unfamiliar source or sources. There is the initial task of locating relevant data sources. Even once this has been accomplished, the problem of answering the query becomes no easier. Issues range from the banal, but nontrivial, task of gaining access privileges, to the more theoretical and complex tasks of regrouping of attributes to form real-world entities (i.e., the attributes within a table must be understood as representations of actual physical objects). Several potential obstacles are discussed below.
The first potential obstacle concerns gaining access to the relevant data source. This involves being allowed to read the database schema and the data contained within the database. Additionally, it may require the ability to store intermediate tables. When a large, multi-step query with several joins or cross products is carried out, the intermediate tables generated need to be temporarily stored. If systems accessing the database are remote, it is clearly impractical to transmit these larger data sets to the querying machine. Thus, some local write space may be desired.
A second potential obstacle concerns the fact that each database in the system may have been designed for efficiency for a system-specific task. Databases are created to fit within larger systems. These systems have certain storage and retrieval requirements, as well as baseline assumptions about data format. No matter how general a database schema is developed, the schema must operate within the system and data requirements. This necessarily means there are queries the system will have difficulty answering.
A third potential problem is that poorly labeled tables and attributes can make it impossible to determine the real-world object being represented. Examples of table names extracted from actual DoD data sources include: $UD01, VNNZ, SYFA, and WUC1. Examples of attribute names extracted from the same DoD source include: SC, TCN, FROM_PPC1, and PRIME. Without the aid of documentation or the original database designers, it is impossible to know what physical objects are represented by these tables. Thus, data corresponding to a user""s query is forever lost because a user or an automated system will be unable to identify all relevant data.
The fourth potential problem in trying answer a query is that documentation is typically scarce and may not be any less cryptic than the database objects themselves. Additionally, original database designers may have forgotten what the objects represent, or they may have moved onto other sites. Users are left to map between database schema and real-world objects to the best of their ability.
If the average user is able to overcome these obstacles and retrieve data from several data sources, he must then combine the responses into a coherent solution set This compilation may involve conflict resolution among data rows. In some situations, it may be acceptable to return both data items and allow the user to decide which data item is more reliable. Consider however a fictitious military example. Two different databases return different locations for the same enemy tank. One location is very close to a U.S. Army base, and the other set of coordinates places the tank much farther away. How should the Army General querying the system react. Should he or she assume the tank is close and ready the troops, and thus risk looking as if the base is preparing for military action? Or should the General not mobilize troops and risk being unprepared for an enemy attack? If the General does not know which data is more accurate in this case, then it is very difficult to determine which results are correct and what action to take.
In addition to the issues discussed in the previous section, attempting to locate relevant databases and achieve accurate query responses in a military environment can be even more difficult. For example, not only does the user need to gain access to a database, but he or she must typically have the appropriate clearance level to see every row and column of the data returned. An even bigger obstacle to overcome is the fact that terminology across branches of the military is not always consistent. First, the same term may have different meanings in different divisions of the military (e.g., rank has different meanings across government military components). Second, the same object (e.g., a 20-foot antenna or a type of ammunition) can have different names in different branches. The first issue leads to a problem in query interpretation while the second creates a problem in retrieving data across databases.
Accordingly, it is an object of the present invention to provide a method and system for allowing a user to import database information into a querying system, such that such information is retrievable.
It is another object of the present invention to provide a method and system for importing database information which does not require a user to have any knowledge of programming languages.
It is still another object of the present invention to provide a method and system for importing database information into a querying system which entails minimal disturbance of the querying system.
It is yet another object of the present invention to provide a querying system and method which aids in the generation of mediators.
It is still another object of the present invention to provide a querying system and methodology which does not utilize a shared representation.
In one aspect of the present invention, the method and system of the present invention are directed to allowing users to import databases into a querying system, such as the system described hereinabove. Once such a database in entered in accordance with the method of the present invention, data in such a database will be easily searchable and retrievable. In this regard, the method and system of the present invention is particularly useful in facilitating future queries since the method of the present invention extracts key information from such databases, the key information to be searched in subsequent queries. Initially, the method may first include the step of receiving from the user instructions relating to how to connect to the remote database that is to be learned about. Information about the name of the database, the port the database is to be connected to, and the full system connection name of the database must be provided. Advantageously, none of this information requires any programming knowledge, and can be provided by the individual responsible for maintaining the database. The methodology may also include the step of receiving from the user instructions concerning how to connect to the data dictionary, if any, that belongs to or is otherwise associated with the database.
More specifically, the method includes the steps of receiving from the user information relating to the particular database and extracting additional database information from the database itself. In the event the database includes a data dictionary, the method includes the step of searching the data dictionary and, if the data dictionary includes a matching entry, the step of extracting the description corresponding to the matching entry and the step of associating key words from the entry with the corresponding information extracted from the database. The method may further include the step of extracting from the data dictionary an expanded name. In the event a data dictionary is not present or otherwise identified, the method includes the step of generating or xe2x80x9cguessingxe2x80x9d an expanded name for the pertinent information extracted from the database. Thereafter, the method includes the steps of classifying objects and entering or storing the information in a database structure, such as a Lightweight Directory Access Protocol (LDAP) directory of a querying system described in more detail hereinbelow. In this regard, once such information is stored in the LDAP, the querying method and system described hereinbelow may be utilized to respond to a user""s query of distributed databases, since certain database information relating to the newly added database has been added.
Once such information has been stored in the database structure, such information is searchable and retrievable in accordance with a universal querying methodology, described hereinbelow. Generally, in this aspect, the present invention is directed to an architecture which allows users to enter unstructured queries, expands and generalizes such queries, and matches the queries to actual target database tables. The method of the present invention generally includes the steps of processing a query (e.g., an unstructured query) to generalize and/or expand the query to return as many relevant words or terms as possible to the user, receiving from the user selected words or terms which the user expects to find in attributes of the distributed databases, and searching a database structure (e.g., an annotated database) having directories extracted from target distributed databases, the directories including table names, attribute names, sample data, and/or, if available, data dictionary information. Of importance, the step of searching the database structure includes utilizing a Lightweight Directory Access Protocol (LDAP), which allows quick access to information directories. Since LDAP directories are designed for reading data rather than updating or adding new data to the directories, the retrieval speed of information contained within the directories (e.g., table names and table attributes) is very fast.
In one aspect of the method of the present invention, the step of processing a query includes the step of receiving at least a first query from a user or client, the first query including at least a first term and the steps of identifying key terms and generalizing and/or expanding the first query to enhance the likelihood of retrieval of relevant data to the user. In this regard, the step of processing at least the first query may include the step of identifying or extracting key words or terms from the first query, such as the first term, since such key words may correspond to an attribute or table name in the target distributed databases. In one embodiment, the step of extracting key words includes the step of extracting at least a first noun and/or a first noun phrase from the first query. In another embodiment, the step of extracting key words comprises the step of extracting at least a first verb from the first query. In yet another embodiment, the step of extracting key words comprises the step of extracting at least a first data item (e.g., part number) in the first query. In order to further generalize the first query in order to enhance the chances of capturing relevant information from target databases, the step of processing at least the first query comprises the step of stemming at least a first term in the first query, such that at least a first root word corresponding to the first term may be utilized in the final search. The processing step may also include the step of generating at least a first synonym of at least the first term of the first unstructured query to expand the scope of the search. The step of processing at least the first query may be facilitated by presenting to the user an initial user query screen, whereby the user is afforded an opportunity to perform various options, including perform stemming, include synonyms, include acronyms, and/or perform wild card substitutions.
Once such nouns, noun phrases, verbs, numbers, synonyms, acronyms, and/or related terms are retrieved and/or generated, the processing step may include the step of presenting such terms to the user in an expanded or refined user query screen format. Such relevant words (e.g., nouns, noun phrases, verbs, numbers, and/or synonyms) may be presented to the user or client to afford the user the opportunity to select the returned relevant terms (e.g., gathered nouns, noun phrases, synonyms, acronyms, data items and/or related items) which the user believes useful in searching the target distributed databases. As a result, the user is able to select or collect terms for which the database schemae will be searched.
In order to facilitate subsequent searches by a user, the step of processing the first unstructured query from the user may further include the step of ranking selected relevant terms (e.g., synonyms or other related terms). In this regard, if a term is selected, the rank for such term is increased and, conversely, if a term is not selected, the rank for such term is decreased. Additionally, the methodology of the present invention is adapted to learn from the structure of the users"" queries. In this regard, if query terms frequently occur together, when a user submits only one of these terms, information regarding both terms may be returned to the user to save the user time. Such learning capability may be included within the LDAP. Conversely, if certain synonyms are not frequently selected, such synonyms will not be returned to users in the future.
As noted hereinabove, the method generally includes the step of searching a database structure, such as an LDAP directory which may include attributes, table names, sample data and/or data dictionary information in the target distributed databases, for attributes and/or table names that match the terms selected by the user (e.g., augmented query terms) and presenting such information to the user. Such attributes and/or table names may be retrieved, along with the remaining attributes for tables that had matching attribute names. A first tree may be constructed, whereby query term folders are populated with database folders containing the tables that match the augmented query terms and such tree is presented or returned to the user. Such folders are labeled with the query term or terms that correspond to the matching tables contained in them. The methodology of the present invention may further include the step of processing a final query from the user. In one embodiment, the step of creating a final query comprises creating a pictorial query for the user, whereby the user is allowed to add constraints and/or joins to produce a final query. The step of processing the final query further includes the step of automatically generating SQL code corresponding to the final query and utilizing a mediator to forward the query to appropriate databases, receive data from each of the appropriate databases, and returning such data to a servlet, where such data is formatted and presented to the user.
In another aspect, the present invention relates to a system for processing at least a first query to retrieve data relevant to the first query from at least a first of a plurality of distributed or target databases. Generally, the system of the present invention includes a computer system for at least receiving a first query from a first user, an extractor for identifying key words, such as nouns, noun phrases, verbs or numbers in the first query, a database structure, such as an LDAP directory, including at least one of a plurality of table names and attributes relating to tables within the distributed databases, the directory being searchable to provide the user, via the computer system, with at least a first database table name and attributes associated with retrieved tables corresponding to the retrieved table names, and a code generator for generating SQL code based upon retrieved table names and/or attributes selected by the user. In order to enhance the search, the system may further include a query generalizer for processing the first query to provide or return to the user via the computer system terms related to at least a first term of the first query to enable the user to select terms the user expects to find in distributed database tables. For purposes of facilitating searching, the system may further include a learning program, whereby information about which synonyms a particular type of user will need and which terms often appear together in these queries is stored and/or ranked. In this regard, after the user enters a query and chooses relevant synonyms, the rank of terms is updated. If terms are selected, the rank of such terms is increased and conversely, if terms are not selected, the rank of such terms is decreased. Further, the system is adapted to learn from the structure of the users"" queries. In this regard, if terms frequently occur together, then when the user asks only about one of these terms, the system will return information about those terms to the user. The system may further include a central mediator for receiving the query and SQL code, via any computer system, the central mediator in communication with the target distributed databases. Such central mediator may be adapted to return the retrieved data from the appropriate distributed databases to the computer system, which is capable of formatting and presenting such data to the user via, for example, a display screen.